<?php
/**
 * Class for parsing XLSX files specifically
 *
 * @author Martins Pilsetnieks
 */

if (!class_exists('XmlIterator\\XmlIterator')) include DIR_EXTENSION."export_import/system/library/SpreadsheetReader/XmlIterator/XmlIterator.php";
use XmlIterator\XmlIterator;

class SpreadsheetReader_XLSX implements Iterator, Countable
{

    private $db = array();
    const CELL_TYPE_BOOL = 'b';
    const CELL_TYPE_NUMBER = 'n';
    const CELL_TYPE_ERROR = 'e';
    const CELL_TYPE_SHARED_STR = 's';
    const CELL_TYPE_STR = 'str';
    const CELL_TYPE_INLINE_STR = 'inlineStr';

    /**
         * Number of shared strings that can be reasonably cached, i.e., that aren't read from file but stored in memory.
         *  If the total number of shared strings is higher than this, caching is not used.
         *  If this value is null, shared strings are cached regardless of amount.
         *  With large shared string caches there are huge performance gains, however a lot of memory could be used which
         *  can be a problem, especially on shared hosting.
         */
    const SHARED_STRING_CACHE_LIMIT = 50000;

    private $Options = array(
        'TempDir' => '',
        'ReturnDateTimeObjects' => false
        );

    private static $RuntimeInfo = array(
        'GMPSupported' => false
        );

    private $Valid = false;

    /**
         * @var SpreadsheetReader_* Handle for the reader object
         */
    private $Handle = false;

    // Worksheet file
    /**
         * @var string Path to the worksheet XML file
         */
    private $WorksheetPath = false;
    /**
         * @var XMLReader XML reader object for the worksheet XML file
         */
    private $Worksheet = false;

        // Shared strings file
    /**
         * @var string Path to shared strings XML file
         */
    private $SharedStringsPath = false;
    /**
         * @var XMLReader XML reader object for the shared strings XML file
         */
    private $SharedStrings = false;
    /**
         * @var array Shared strings cache, if the number of shared strings is low enough
         */
    private $SharedStringCache = array();

        // Workbook data
    /**
         * @var SimpleXMLElement XML object for the workbook XML file
         */
    private $WorkbookXML = false;

        // Style data
    /**
         * @var SimpleXMLElement XML object for the styles XML file
         */
    private $StylesXML = false;
    /**
         * @var array Container for cell value style data
         */
    private $Styles = array();

    private $TempDir = '';
    private $TempFiles = array();

    private $CurrentRow = false;

        // Runtime parsing data
    /**
         * @var int Current row in the file
         */
    private $Index = 0;

    /**
         * @var array Data about separate sheets in the file
         */
    private $Sheets = false;

    private $SharedStringCount = 0;
    private $SharedStringIndex = 0;
    private $LastSharedStringValue = null;

    private $RowOpen = false;

    private $SSOpen = false;
    private $SSForwarded = false;

    private static $BuiltinFormats = array(
        0 => '',
        1 => '0',
        2 => '0.00',
        3 => '#,##0',
        4 => '#,##0.00',

        9 => '0%',
        10 => '0.00%',
        11 => '0.00E+00',
        12 => '# ?/?',
        13 => '# ??/??',
        14 => 'mm-dd-yy',
        15 => 'd-mmm-yy',
        16 => 'd-mmm',
        17 => 'mmm-yy',
        18 => 'h:mm AM/PM',
        19 => 'h:mm:ss AM/PM',
        20 => 'h:mm',
        21 => 'h:mm:ss',
        22 => 'm/d/yy h:mm',

        37 => '#,##0 ;(#,##0)',
        38 => '#,##0 ;[Red](#,##0)',
        39 => '#,##0.00;(#,##0.00)',
        40 => '#,##0.00;[Red](#,##0.00)',

        45 => 'mm:ss',
        46 => '[h]:mm:ss',
        47 => 'mmss.0',
        48 => '##0.0E+0',
        49 => '@',

            // CHT & CHS
        27 => '[$-404]e/m/d',
        30 => 'm/d/yy',
        36 => '[$-404]e/m/d',
        50 => '[$-404]e/m/d',
        57 => '[$-404]e/m/d',

            // THA
        59 => 't0',
        60 => 't0.00',
        61 =>'t#,##0',
        62 => 't#,##0.00',
        67 => 't0%',
        68 => 't0.00%',
        69 => 't# ?/?',
        70 => 't# ??/??'
        );
    private $Formats = array();

    private static $DateReplacements = array(
        'All' => array(
            '\\' => '',
            'am/pm' => 'A',
            'yyyy' => 'Y',
            'yy' => 'y',
            'mmmmm' => 'M',
            'mmmm' => 'F',
            'mmm' => 'M',
            ':mm' => ':i',
            'mm' => 'm',
            'm' => 'n',
            'dddd' => 'l',
            'ddd' => 'D',
            'dd' => 'd',
            'd' => 'j',
            'ss' => 's',
            '.s' => ''
            ),
        '24H' => array(
            'hh' => 'H',
            'h' => 'G'
            ),
        '12H' => array(
            'hh' => 'h',
            'h' => 'G'
            )
        );

    private static $BaseDate = false;
    private static $DecimalSeparator = '.';
    private static $ThousandSeparator = '';
    private static $CurrencyCode = '';

    /**
         * @var array Cache for already processed format strings
         */
    private $ParsedFormatCache = array();

    /**
         * @param string Path to file
         * @param array Options:
         *  TempDir => string Temporary directory path
         *  ReturnDateTimeObjects => bool True => dates and times will be returned as PHP DateTime objects, false => as strings
         */
    public function __construct($registry, $Filepath, array $Options = null)
    {
        $this->db = $registry->get('db');

        if (!is_readable($Filepath))
        {
            throw new Exception('SpreadsheetReader_XLSX: File not readable ('.$Filepath.')');
        }

        $this -> TempDir = isset($Options['TempDir']) && is_writable($Options['TempDir']) ?
        $Options['TempDir'] :
        sys_get_temp_dir();

        $this -> TempDir = rtrim($this -> TempDir, DIRECTORY_SEPARATOR);
        $this -> TempDir = $this -> TempDir.DIRECTORY_SEPARATOR.uniqid().DIRECTORY_SEPARATOR;

        $Zip = new ZipArchive;
        $Status = $Zip -> open($Filepath);

        if ($Status !== true)
        {
            throw new Exception('SpreadsheetReader_XLSX: File not readable ('.$Filepath.') (Error '.$Status.')');
        }

            // Getting the general workbook information
        if ($Zip -> locateName('xl/workbook.xml') !== false)
        {
            $this -> WorkbookXML = new SimpleXMLElement($Zip -> getFromName('xl/workbook.xml'));
        }

            // Extracting the XMLs from the XLSX zip file
        if ($Zip -> locateName('xl/sharedStrings.xml') !== false)
        {
            $this -> SharedStringsPath = $this -> TempDir.'xl'.DIRECTORY_SEPARATOR.'sharedStrings.xml';
            $Zip -> extractTo($this -> TempDir, 'xl/sharedStrings.xml');
            $this -> TempFiles[] = $this -> TempDir.'xl'.DIRECTORY_SEPARATOR.'sharedStrings.xml';

            if (is_readable($this -> SharedStringsPath))
            {
                    // $this -> SharedStrings = new XMLReader;
                    // $this -> SharedStrings -> open($this -> SharedStringsPath);
                $it = new XmlIterator($this -> SharedStringsPath, "si");

                $this->db->query("CREATE TEMPORARY TABLE IF NOT EXISTS `".DB_PREFIX."dei_shared_strings` (
                    `index` INT(11) NOT NULL,
                    `value` TEXT NOT NULL,
                    PRIMARY KEY (`index`)
                    ) COLLATE='utf8_general_ci' ENGINE=InnoDB;");

                $implode = array();
                foreach ($it as $k => $v) {
                    if(count($implode) >= 10){
                        $this->db->query("INSERT INTO `".DB_PREFIX."dei_shared_strings` (`index`, `value`) VALUES ".implode(',', $implode)."");
                        $implode = array();
                    }
                    if(!empty($v['t'])){
                        $implode[] = "('".$k."','".$this->db->escape($v['t'])."')";
                    }
                }
                if(count($implode) > 0){
                    $this->db->query("INSERT INTO `".DB_PREFIX."dei_shared_strings` (`index`, `value`) VALUES ".implode(',', $implode)."");
                }
                    // $this -> PrepareSharedStringCache();
            }
        }

        $Sheets = $this -> Sheets();

        foreach ($this -> Sheets as $Index => $Name)
        {
            if ($Zip -> locateName('xl/worksheets/sheet'.$Index.'.xml') !== false)
            {
                $Zip -> extractTo($this -> TempDir, 'xl/worksheets/sheet'.$Index.'.xml');
                $this -> TempFiles[] = $this -> TempDir.'xl'.DIRECTORY_SEPARATOR.'worksheets'.DIRECTORY_SEPARATOR.'sheet'.$Index.'.xml';
            }
        }

        $this -> ChangeSheet(0);

            // If worksheet is present and is OK, parse the styles already
        if ($Zip -> locateName('xl/styles.xml') !== false)
        {
            $this -> StylesXML = new SimpleXMLElement($Zip -> getFromName('xl/styles.xml'));
            if ($this -> StylesXML && $this -> StylesXML -> cellXfs && $this -> StylesXML -> cellXfs -> xf)
            {
                foreach ($this -> StylesXML -> cellXfs -> xf as $Index => $XF)
                {
                        // Format #0 is a special case - it is the "General" format that is applied regardless of applyNumberFormat
                    if ($XF -> attributes() -> applyNumberFormat || (0 == (int)$XF -> attributes() -> numFmtId))
                    {
                        $FormatId = (int)$XF -> attributes() -> numFmtId;
                            // If format ID >= 164, it is a custom format and should be read from styleSheet\numFmts
                        $this -> Styles[] = $FormatId;
                    }
                    else
                    {
                            // 0 for "General" format
                        $this -> Styles[] = 0;
                    }
                }
            }

            if ($this -> StylesXML -> numFmts && $this -> StylesXML -> numFmts -> numFmt)
            {
                foreach ($this -> StylesXML -> numFmts -> numFmt as $Index => $NumFmt)
                {
                    $this -> Formats[(int)$NumFmt -> attributes() -> numFmtId] = (string)$NumFmt -> attributes() -> formatCode;
                }
            }

            unset($this -> StylesXML);
        }

        $Zip -> close();

            // Setting base date
        if (!self::$BaseDate)
        {
            self::$BaseDate = new DateTime;
            self::$BaseDate -> setTimezone(new DateTimeZone('UTC'));
            self::$BaseDate -> setDate(1900, 1, 0);
            self::$BaseDate -> setTime(0, 0, 0);
        }

            // Decimal and thousand separators
        if (!self::$DecimalSeparator && !self::$ThousandSeparator && !self::$CurrencyCode)
        {
            $Locale = localeconv();
            self::$DecimalSeparator = $Locale['decimal_point'];
            self::$ThousandSeparator = $Locale['thousands_sep'];
            self::$CurrencyCode = $Locale['int_curr_symbol'];
        }

        if (function_exists('gmp_gcd'))
        {
            self::$RuntimeInfo['GMPSupported'] = true;
        }
    }

    /**
         * Destructor, destroys all that remains (closes and deletes temp files)
         */
    public function __destruct()
    {
        foreach ($this -> TempFiles as $TempFile)
        {
            @unlink($TempFile);
        }

            // Better safe than sorry - shouldn't try deleting '.' or '/', or '..'.
        if (strlen($this -> TempDir) > 2)
        {
            @rmdir($this -> TempDir.'xl'.DIRECTORY_SEPARATOR.'worksheets');
            @rmdir($this -> TempDir.'xl');
            @rmdir($this -> TempDir);
        }

        if ($this -> Worksheet && $this -> Worksheet instanceof XMLReader)
        {
            $this -> Worksheet -> close();
            unset($this -> Worksheet);
        }
        unset($this -> WorksheetPath);

            // $this->db->query("DROP TABLE IF EXISTS `".DB_PREFIX."dei_shared_strings`");

        if ($this -> SharedStrings && $this -> SharedStrings instanceof XMLReader)
        {
            $this -> SharedStrings -> close();
            unset($this -> SharedStrings);
        }
        unset($this -> SharedStringsPath);

        if (isset($this -> StylesXML))
        {
            unset($this -> StylesXML);
        }
        if ($this -> WorkbookXML)
        {
            unset($this -> WorkbookXML);
        }
    }

    /**
         * Retrieves an array with information about sheets in the current file
         *
         * @return array List of sheets (key is sheet index, value is name)
         */
    public function Sheets()
    {
        if ($this -> Sheets === false)
        {
            $this -> Sheets = array();
            foreach ($this -> WorkbookXML -> sheets -> sheet as $Index => $Sheet)
            {
                $Attributes = $Sheet -> attributes();
                foreach ($Attributes as $Name => $Value)
                {
                    if($Name == "sheetId"){
                        $SheetID = (string)$Value;
                        break;
                    }
                    if ($Name == 'id')
                    {
                       $SheetID = (int)str_replace('rId', '', (string)$Value);
                       break;
                   }
               }

               $this -> Sheets[$SheetID] = (string)$Sheet['name'];
           }
           ksort($this -> Sheets);
       }
       return array_values($this -> Sheets);
   }

    /**
         * Changes the current sheet in the file to another
         *
         * @param int Sheet index
         *
         * @return bool True if sheet was successfully changed, false otherwise.
         */
    public function ChangeSheet($Index)
    {
        $RealSheetIndex = false;
        $Sheets = $this -> Sheets();
        if (isset($Sheets[$Index]))
        {
            $SheetIndexes = array_keys($this -> Sheets);
            $RealSheetIndex = $SheetIndexes[$Index];
        }

        $TempWorksheetPath = $this -> TempDir.'xl/worksheets/sheet'.$RealSheetIndex.'.xml';

        if ($RealSheetIndex !== false && is_readable($TempWorksheetPath))
        {
            $this -> WorksheetPath = $TempWorksheetPath;

            $this -> rewind();
            return true;
        }

        return false;
    }

    /**
         * Creating shared string cache if the number of shared strings is acceptably low (or there is no limit on the amount
         */
    private function PrepareSharedStringCache()
    {
        while ($this -> SharedStrings -> read())
        {
            if ($this -> SharedStrings -> name == 'sst')
            {
                $this -> SharedStringCount = $this -> SharedStrings -> getAttribute('count');
                break;
            }
        }

        if (!$this -> SharedStringCount || (self::SHARED_STRING_CACHE_LIMIT < $this -> SharedStringCount && self::SHARED_STRING_CACHE_LIMIT !== null))
        {
            return false;
        }

        $CacheIndex = 0;
        $CacheValue = '';
        while ($this -> SharedStrings -> read())
        {
            switch ($this -> SharedStrings -> name)
            {
                case 'si':
                if ($this -> SharedStrings -> nodeType == XMLReader::END_ELEMENT)
                {
                    $this -> SharedStringCache[$CacheIndex] = $CacheValue;
                    $CacheIndex++;
                    $CacheValue = '';
                }
                break;
                case 't':
                if ($this -> SharedStrings -> nodeType == XMLReader::END_ELEMENT)
                {
                    continue 2;
                }
                $CacheValue .= $this -> SharedStrings -> readString();
                break;
            }
        }

        $this -> SharedStrings -> close();
        return true;
    }

    /**
         * Retrieves a shared string value by its index
         *
         * @param int Shared string index
         *
         * @return string Value
         */
    private function GetSharedString($Index)
    {

        $query = $this->db->query("SELECT `value` FROM `".DB_PREFIX."dei_shared_strings` WHERE `index` = '".(int)$Index."'");
        if($query->num_rows){
            return $query->row['value'];
        }
        else{
            return '';
        }


        if ((self::SHARED_STRING_CACHE_LIMIT === null || self::SHARED_STRING_CACHE_LIMIT > 0) && !empty($this -> SharedStringCache))
        {
            if (isset($this -> SharedStringCache[$Index]))
            {
                return $this -> SharedStringCache[$Index];
            }
            else
            {
                return '';
            }
        }

            // If the desired index is before the current, rewind the XML
        if ($this -> SharedStringIndex > $Index)
        {
            $this -> SSOpen = false;
            $this -> SharedStrings -> close();
            $this -> SharedStrings -> open($this -> SharedStringsPath);
            $this -> SharedStringIndex = 0;
            $this -> LastSharedStringValue = null;
            $this -> SSForwarded = false;
        }

            // Finding the unique string count (if not already read)
        if ($this -> SharedStringIndex == 0 && !$this -> SharedStringCount)
        {
            while ($this -> SharedStrings -> read())
            {
                if ($this -> SharedStrings -> name == 'sst')
                {
                    $this -> SharedStringCount = $this -> SharedStrings -> getAttribute('uniqueCount');
                    break;
                }
            }
        }

            // If index of the desired string is larger than possible, don't even bother.
        if ($this -> SharedStringCount && ($Index >= $this -> SharedStringCount))
        {
            return '';
        }

            // If an index with the same value as the last already fetched is requested
            // (any further traversing the tree would get us further away from the node)
        if (($Index == $this -> SharedStringIndex) && ($this -> LastSharedStringValue !== null))
        {
            return $this -> LastSharedStringValue;
        }

            // Find the correct <si> node with the desired index
        while ($this -> SharedStringIndex <= $Index)
        {
                // SSForwarded is set further to avoid double reading in case nodes are skipped.
            if ($this -> SSForwarded)
            {
                $this -> SSForwarded = false;
            }
            else
            {
                $ReadStatus = $this -> SharedStrings -> read();
                if (!$ReadStatus)
                {
                    break;
                }
            }

            if ($this -> SharedStrings -> name == 'si')
            {
                if ($this -> SharedStrings -> nodeType == XMLReader::END_ELEMENT)
                {
                    $this -> SSOpen = false;
                    $this -> SharedStringIndex++;
                }
                else
                {
                    $this -> SSOpen = true;

                    if ($this -> SharedStringIndex < $Index)
                    {
                        $this -> SSOpen = false;
                        $this -> SharedStrings -> next('si');
                        $this -> SSForwarded = true;
                        $this -> SharedStringIndex++;
                        continue;
                    }
                    else
                    {
                        break;
                    }
                }
            }
        }

        $Value = '';

            // Extract the value from the shared string
        if ($this -> SSOpen && ($this -> SharedStringIndex == $Index))
        {
            while ($this -> SharedStrings -> read())
            {
                switch ($this -> SharedStrings -> name)
                {
                    case 't':
                    if ($this -> SharedStrings -> nodeType == XMLReader::END_ELEMENT)
                    {
                        continue 2;
                    }
                    $Value .= $this -> SharedStrings -> readString();
                    break;
                    case 'si':
                    if ($this -> SharedStrings -> nodeType == XMLReader::END_ELEMENT)
                    {
                        $this -> SSOpen = false;
                        $this -> SSForwarded = true;
                        break 2;
                    }
                    break;
                }
            }
        }

        if ($Value)
        {
            $this -> LastSharedStringValue = $Value;
        }
        return $Value;
    }

    /**
         * Formats the value according to the index
         *
         * @param string Cell value
         * @param int Format index
         *
         * @return string Formatted cell value
         */
    private function FormatValue($Value, $Index)
    {
        if (!is_numeric($Value))
        {
            return $Value;
        }

        if (isset($this -> Styles[$Index]) && ($this -> Styles[$Index] !== false))
        {
            $Index = $this -> Styles[$Index];
        }
        else
        {
            return $Value;
        }

            // A special case for the "General" format
        if ($Index == 0)
        {
            return $this -> GeneralFormat($Value);
        }

        $Format = array();

        if (isset($this -> ParsedFormatCache[$Index]))
        {
            $Format = $this -> ParsedFormatCache[$Index];
        }

        if (!$Format)
        {
            $Format = array(
                'Code' => false,
                'Type' => false,
                'Scale' => 1,
                'Thousands' => false,
                'Currency' => false
                );

            if (isset(self::$BuiltinFormats[$Index]))
            {
                $Format['Code'] = self::$BuiltinFormats[$Index];
            }
            elseif (isset($this -> Formats[$Index]))
            {
                $Format['Code'] = $this -> Formats[$Index];
            }

                // Format code found, now parsing the format
            if ($Format['Code'])
            {
                $Sections = explode(';', $Format['Code']);
                $Format['Code'] = $Sections[0];

                switch (count($Sections))
                {
                    case 2:
                    if ($Value < 0)
                    {
                        $Format['Code'] = $Sections[1];
                    }
                    break;
                    case 3:
                    case 4:
                    if ($Value < 0)
                    {
                        $Format['Code'] = $Sections[1];
                    }
                    elseif ($Value == 0)
                    {
                        $Format['Code'] = $Sections[2];
                    }
                    break;
                }
            }

                // Stripping colors
            $Format['Code'] = trim(preg_replace('{^\[[[:alpha:]]+\]}i', '', $Format['Code']));

                // Percentages
            if (substr($Format['Code'], -1) == '%')
            {
                $Format['Type'] = 'Percentage';
            }
            elseif (preg_match('{^(\[\$[[:alpha:]]*-[0-9A-F]*\])*[hmsdy]}i', $Format['Code']))
            {
                $Format['Type'] = 'DateTime';

                $Format['Code'] = trim(preg_replace('{^(\[\$[[:alpha:]]*-[0-9A-F]*\])}i', '', $Format['Code']));
                $Format['Code'] = strtolower($Format['Code']);

                $Format['Code'] = strtr($Format['Code'], self::$DateReplacements['All']);
                if (strpos($Format['Code'], 'A') === false)
                {
                    $Format['Code'] = strtr($Format['Code'], self::$DateReplacements['24H']);
                }
                else
                {
                    $Format['Code'] = strtr($Format['Code'], self::$DateReplacements['12H']);
                }
            }
            elseif ($Format['Code'] == '[$EUR ]#,##0.00_-')
            {
                $Format['Type'] = 'Euro';
            }
            else
            {
                    // Removing skipped characters
                $Format['Code'] = preg_replace('{_.}', '', $Format['Code']);
                    // Removing unnecessary escaping
                $Format['Code'] = preg_replace("{\\\\}", '', $Format['Code']);
                    // Removing string quotes
                $Format['Code'] = str_replace(array('"', '*'), '', $Format['Code']);
                    // Removing thousands separator
                if (strpos($Format['Code'], '0,0') !== false || strpos($Format['Code'], '#,#') !== false)
                {
                    $Format['Thousands'] = true;
                }
                $Format['Code'] = str_replace(array('0,0', '#,#'), array('00', '##'), $Format['Code']);

                    // Scaling (Commas indicate the power)
                $Scale = 1;
                $Matches = array();
                if (preg_match('{(0|#)(,+)}', $Format['Code'], $Matches))
                {
                    $Scale = pow(1000, strlen($Matches[2]));
                        // Removing the commas
                    $Format['Code'] = preg_replace(array('{0,+}', '{#,+}'), array('0', '#'), $Format['Code']);
                }

                $Format['Scale'] = $Scale;

                if (preg_match('{#?.*\?\/\?}', $Format['Code']))
                {
                    $Format['Type'] = 'Fraction';
                }
                else
                {
                    $Format['Code'] = str_replace('#', '', $Format['Code']);

                    $Matches = array();
                    if (preg_match('{(0+)(\.?)(0*)}', preg_replace('{\[[^\]]+\]}', '', $Format['Code']), $Matches))
                    {
                        $Integer = $Matches[1];
                        $DecimalPoint = $Matches[2];
                        $Decimals = $Matches[3];

                        $Format['MinWidth'] = strlen($Integer) + strlen($DecimalPoint) + strlen($Decimals);
                        $Format['Decimals'] = $Decimals;
                        $Format['Precision'] = strlen($Format['Decimals']);
                        $Format['Pattern'] = '%0'.$Format['MinWidth'].'.'.$Format['Precision'].'f';
                    }
                }

                $Matches = array();
                if (preg_match('{\[\$(.*)\]}u', $Format['Code'], $Matches))
                {
                    $CurrFormat = $Matches[0];
                    $CurrCode = $Matches[1];
                    $CurrCode = explode('-', $CurrCode);
                    if ($CurrCode)
                    {
                        $CurrCode = $CurrCode[0];
                    }

                    if (!$CurrCode)
                    {
                        $CurrCode = self::$CurrencyCode;
                    }

                    $Format['Currency'] = $CurrCode;
                }
                $Format['Code'] = trim($Format['Code']);
            }

            $this -> ParsedFormatCache[$Index] = $Format;
        }

            // Applying format to value
        if ($Format)
        {
            if ($Format['Code'] == '@')
            {
                return (string)$Value;
            }
                // Percentages
            elseif ($Format['Type'] == 'Percentage')
            {
                if ($Format['Code'] === '0%')
                {
                    $Value = round(100 * $Value, 0).'%';
                }
                else
                {
                    $Value = sprintf('%.2f%%', round(100 * $Value, 2));
                }
            }
                // Dates and times
            elseif ($Format['Type'] == 'DateTime')
            {
                $Days = (int)$Value;
                    // Correcting for Feb 29, 1900
                if ($Days > 60)
                {
                    $Days--;
                }

                    // At this point time is a fraction of a day
                $Time = ($Value - (int)$Value);
                $Seconds = 0;
                if ($Time)
                {
                        // Here time is converted to seconds
                        // Some loss of precision will occur
                    $Seconds = (int)($Time * 86400);
                }

                $Value = clone self::$BaseDate;
                $Value -> add(new DateInterval('P'.$Days.'D'.($Seconds ? 'T'.$Seconds.'S' : '')));

                if (!$this -> Options['ReturnDateTimeObjects'])
                {
                    $Value = $Value -> format($Format['Code']);
                }
                else
                {
                        // A DateTime object is returned
                }
            }
            elseif ($Format['Type'] == 'Euro')
            {
                $Value = 'EUR '.sprintf('%1.2f', $Value);
            }
            else
            {
                    // Fractional numbers
                if ($Format['Type'] == 'Fraction' && ($Value != (int)$Value))
                {
                    $Integer = floor(abs($Value));
                    $Decimal = fmod(abs($Value), 1);
                        // Removing the integer part and decimal point
                    $Decimal *= pow(10, strlen($Decimal) - 2);
                    $DecimalDivisor = pow(10, strlen($Decimal));

                    if (self::$RuntimeInfo['GMPSupported'])
                    {
                        $GCD = gmp_strval(gmp_gcd($Decimal, $DecimalDivisor));
                    }
                    else
                    {
                        $GCD = self::GCD($Decimal, $DecimalDivisor);
                    }

                    $AdjDecimal = $DecimalPart/$GCD;
                    $AdjDecimalDivisor = $DecimalDivisor/$GCD;

                    if (
                        strpos($Format['Code'], '0') !== false || 
                        strpos($Format['Code'], '#') !== false ||
                        substr($Format['Code'], 0, 3) == '? ?'
                        )
                    {
                            // The integer part is shown separately apart from the fraction
                        $Value = ($Value < 0 ? '-' : '').
                        $Integer ? $Integer.' ' : ''.
                        $AdjDecimal.'/'.
                        $AdjDecimalDivisor;
                    }
                    else
                    {
                            // The fraction includes the integer part
                        $AdjDecimal += $Integer * $AdjDecimalDivisor;
                        $Value = ($Value < 0 ? '-' : '').
                        $AdjDecimal.'/'.
                        $AdjDecimalDivisor;
                    }
                }
                else
                {
                        // Scaling
                    $Value = $Value / $Format['Scale'];

                    if (!empty($Format['MinWidth']) && $Format['Decimals'])
                    {
                        if ($Format['Thousands'])
                        {
                            $Value = number_format($Value, $Format['Precision'],
                                self::$DecimalSeparator, self::$ThousandSeparator);
                        }
                        else
                        {
                            $Value = sprintf($Format['Pattern'], $Value);
                        }

                        $Value = preg_replace('{(0+)(\.?)(0*)}', $Value, $Format['Code']);
                    }
                }

                    // Currency/Accounting
                if ($Format['Currency'])
                {
                  $Value = preg_replace('', $Format['Currency'], $Value);
              }
          }

      }

      return $Value;
  }

    /**
         * Attempts to approximate Excel's "general" format.
         *
         * @param mixed Value
         *
         * @return mixed Result
         */
    public function GeneralFormat($Value)
    {
            // Numeric format
        if (is_numeric($Value))
        {
            $Value = (float)$Value;
        }
        return $Value;
    }

        // !Iterator interface methods
    /** 
         * Rewind the Iterator to the first element.
         * Similar to the reset() function for arrays in PHP
         */ 
    public function rewind(): void
    {
            // Removed the check whether $this -> Index == 0 otherwise ChangeSheet doesn't work properly

            // If the worksheet was already iterated, XML file is reopened.
            // Otherwise it should be at the beginning anyway
        if ($this -> Worksheet instanceof XMLReader)
        {
            $this -> Worksheet -> close();
        }
        else
        {
            $this -> Worksheet = new XMLReader;
        }

        $this -> Worksheet -> open($this -> WorksheetPath);

        $this -> Valid = true;
        $this -> RowOpen = false;
        $this -> CurrentRow = false;
        $this -> Index = 0;
    }

    /**
         * Return the current element.
         * Similar to the current() function for arrays in PHP
         *
         * @return mixed current element from the collection
         */
    public function current(): mixed
    {
        if ($this -> Index == 0 && $this -> CurrentRow === false)
        {
            $this -> next();
            $this -> Index--;
        }
        return $this -> CurrentRow;
    }

    /** 
         * Move forward to next element. 
         * Similar to the next() function for arrays in PHP 
         */ 
    public function next(): void
    {
        $this -> Index++;

        $this -> CurrentRow = array();

        if (!$this -> RowOpen)
        {
            while ($this -> Valid = $this -> Worksheet -> read())
            {
                if ($this -> Worksheet -> name == 'row')
                {
                        // Getting the row spanning area (stored as e.g., 1:12)
                        // so that the last cells will be present, even if empty
                    $RowSpans = $this -> Worksheet -> getAttribute('spans');
                    if ($RowSpans)
                    {
                        $RowSpans = explode(':', $RowSpans);
                        $CurrentRowColumnCount = $RowSpans[1];
                    }
                    else
                    {
                        $CurrentRowColumnCount = 0;
                    }

                    if ($CurrentRowColumnCount > 0)
                    {
                        $this -> CurrentRow = array_fill(0, $CurrentRowColumnCount, '');
                    }

                    $this -> RowOpen = true;
                    break;
                }
            }
        }

            // Reading the necessary row, if found
        if ($this -> RowOpen)
        {
                // These two are needed to control for empty cells
            $MaxIndex = 0;
            $CellCount = 0;

            $CellHasSharedString = false;

            while ($this -> Valid = $this -> Worksheet -> read())
            {
                switch ($this -> Worksheet -> name)
                {
            //          // End of row
                    case 'row':
                    if ($this -> Worksheet -> nodeType == XMLReader::END_ELEMENT)
                    {
                        $this -> RowOpen = false;
                        break 2;
                    }
                    break;
                        // Cell
                    case 'c':
                            // If it is a closing tag, skip it
                    if ($this -> Worksheet -> nodeType == XMLReader::END_ELEMENT)
                    {
                        continue 2;
                    }

                    $StyleId = (int)$this -> Worksheet -> getAttribute('s');

                            // Get the index of the cell
                    $Index = $this -> Worksheet -> getAttribute('r');
                    $Letter = preg_replace('{[^[:alpha:]]}S', '', $Index);
                    $Index = self::IndexFromColumnLetter($Letter);

                            // Determine cell type
                    if ($this -> Worksheet -> getAttribute('t') == self::CELL_TYPE_SHARED_STR)
                    {
                        $CellHasSharedString = true;
                    }
                    else
                    {
                        $CellHasSharedString = false;
                    }

                    $this -> CurrentRow[$Index] = '';

                    $CellCount++;
                    if ($Index > $MaxIndex)
                    {
                        $MaxIndex = $Index;
                    }

                    break;
                        // Cell value
                    case 'v':
                    case 'is':
                    if ($this -> Worksheet -> nodeType == XMLReader::END_ELEMENT)
                    {
                        continue 2;
                    }

                    $Value = $this -> Worksheet -> readString();

                    if ($CellHasSharedString)
                    {
                        $Value = $this -> GetSharedString($Value);
                    }

                            // Format value if necessary
                    if ($Value !== '' && $StyleId && isset($this -> Styles[$StyleId]))
                    {
                        $Value = $this -> FormatValue($Value, $StyleId);
                    }
                    elseif ($Value)
                    {
                        $Value = $this -> GeneralFormat($Value);
                    }

                    $this -> CurrentRow[$Index] = $Value;
                    break;
                }
            }

                // Adding empty cells, if necessary
                // Only empty cells inbetween and on the left side are added
            if ($MaxIndex + 1 > $CellCount)
            {
                $this -> CurrentRow = $this -> CurrentRow + array_fill(0, $MaxIndex + 1, '');
                ksort($this -> CurrentRow);
            }
        }

        //return $this -> CurrentRow;
    }

    public function setKey($index){
        $this -> Index = $index;
    }

    /** 
         * Return the identifying key of the current element.
         * Similar to the key() function for arrays in PHP
         *
         * @return mixed either an integer or a string
         */ 
    public function key(): mixed
    {
        return $this -> Index;
    }

    /** 
         * Check if there is a current element after calls to rewind() or next().
         * Used to check if we've iterated to the end of the collection
         *
         * @return boolean FALSE if there's nothing more to iterate over
         */ 
    public function valid(): bool
    {
        return $this -> Valid;
    }

        // !Countable interface method
    /**
         * Ostensibly should return the count of the contained items but this just returns the number
         * of rows read so far. It's not really correct but at least coherent.
         */
    public function count(): int
    {
        return $this -> Index + 1;
    }

    /**
         * Takes the column letter and converts it to a numerical index (0-based)
         *
         * @param string Letter(s) to convert
         *
         * @return mixed Numeric index (0-based) or boolean false if it cannot be calculated
         */
    public static function IndexFromColumnLetter($Letter)
    {
        $Powers = array();

        $Letter = strtoupper($Letter);

        $Result = 0;
        for ($i = strlen($Letter) - 1, $j = 0; $i >= 0; $i--, $j++)
        {
            $Ord = ord($Letter[$i]) - 64;
            if ($Ord > 26)
            {
                    // Something is very, very wrong
                return false;
            }
            $Result += $Ord * pow(26, $j);
        }
        return $Result - 1;
    }

    /**
         * Helper function for greatest common divisor calculation in case GMP extension is
         *  not enabled
         *
         * @param int Number #1
         * @param int Number #2
         *
         * @param int Greatest common divisor
         */
    public static function GCD($A, $B)
    {
        $A = abs($A);
        $B = abs($B);
        if ($A + $B == 0)
        {
            return 0;
        }
        else
        {
            $C = 1;

            while ($A > 0)
            {
                $C = $A;
                $A = $B % $A;
                $B = $C;
            }

            return $C;
        }
    }
}
?>
